package com.example.se2_funmessages;

import java.io.IOException;
import java.util.ArrayList;
import java.util.concurrent.ExecutionException;
import java.io.StringReader;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.ResponseHandler;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.DefaultHttpClient;
import org.w3c.dom.CharacterData;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import com.example.se2_funmessages.SendMessage.SendMessageTask;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.AsyncTask;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "messages.db";
	private static final int DATABASE_VERSION = 1;

	private static int current_user_id;
	private static String current_user_username;
	private static String current_user_password;

	public DatabaseHandler(Context context) {
		// Log.d("DBC", "Database Constructor");
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	public void insertUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();
		current_user_id = user.id;
		current_user_username = user.username;
		current_user_password = user.password;

		String DELETE = "DELETE FROM user";

		String INSERT_USER = "INSERT INTO user (Id,Username,Password) values ("
				+ "'" + current_user_id + "'" + ", " + "'"
				+ current_user_username + "'" + ", " + "'"
				+ current_user_password + "'" + ");";

		db.execSQL(DELETE);

		db.execSQL(INSERT_USER);
		db.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_LOCA_MESSAGES_TABLE = "CREATE TABLE message ("
				+ "Message_Id INTEGER PRIMARY KEY ," + "To_Id INTEGER,"
				+ " From_id INTEGER, " + " Body TEXT, " + " Date TEXT)";

		db.execSQL(CREATE_LOCA_MESSAGES_TABLE);

		String CREATE_LOCA_MESSAGES_SAVED_TABLE = "CREATE TABLE message_saved ("
				+ "Message_Id INTEGER PRIMARY KEY AUTOINCREMENT ," + "To_Id INTEGER,"
				+ " From_id INTEGER, " + " Body TEXT, " + " Date TEXT)";

		db.execSQL(CREATE_LOCA_MESSAGES_SAVED_TABLE);

		String CREATE_LOCA_USER_TABLE = "CREATE TABLE user ("
				+ "Id INTEGER PRIMARY KEY ," + "Username TEXT,"
				+ " Password TEXT)";

		db.execSQL(CREATE_LOCA_USER_TABLE);

	}

	public int getUserNum() {
		SQLiteDatabase db = this.getWritableDatabase();

		String SQL = "SELECT * FROM USER";

		int toReturn = -1;

		Cursor cursor = db.rawQuery(SQL, null);

		if (cursor.moveToFirst()) {

			toReturn = Integer.parseInt(cursor.getString(0));

		}
		cursor.close();

		db.close();

		// return contact list
		return toReturn;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}

	public void logoutCurrentUser() {
		SQLiteDatabase db = this.getWritableDatabase();

		int userId = -1;

		Login x = new Login();
		userId = x.currentUserId;
		String sql = "DELETE FROM user WHERE Id =" + userId;

		db.execSQL(sql);

		db.close();

	}
	
	public int getMessageCount(){
		SQLiteDatabase db = this.getWritableDatabase();
	
		int userId = -1;

		Login x = new Login();
		userId = x.currentUserId;
		
		Cursor dataCount = db.rawQuery("select count(*) from message WHERE To_Id = " + userId, null);
        dataCount.moveToFirst();
        int count = dataCount.getInt(0);
        dataCount.close();
        return count;
	}
	
	public int getSavedMessageCount(){
		SQLiteDatabase db = this.getWritableDatabase();
		
		int userId = -1;
  
		Login x = new Login();
		userId = x.currentUserId;
		  
		Cursor dataCount = db.rawQuery("select count(*) from message_saved WHERE To_Id = " + userId, null);
        dataCount.moveToFirst();
        int count = dataCount.getInt(0);
        dataCount.close();
        return count;
	}

	public void insertMessagesIntoDatabase(String response) {
		int userId = -1;

		Login l = new Login();
		userId = l.currentUserId;

		DocumentBuilder db = null;
		try {
			db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
		} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		InputSource is = new InputSource();
		is.setCharacterStream(new StringReader(response));

		Document doc = null;
		try {
			doc = db.parse(is);
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		NodeList nodes = doc.getElementsByTagName("wholeMessage");

		ArrayList<Message> arrMess = new ArrayList<Message>();

		for (int i = 0; i < nodes.getLength(); i++) {

			Element element = (Element) nodes.item(i);

			Message m = new Message();

			NodeList name = element.getElementsByTagName("message");
			Element line = (Element) name.item(0);
			m.body = getCharacterDataFromElement(line);

			NodeList messageId = element.getElementsByTagName("messageId");
			line = (Element) messageId.item(0);
			m.Message_Id = Integer.parseInt(getCharacterDataFromElement(line));

			NodeList userIdTo = element.getElementsByTagName("userIdTo");
			line = (Element) userIdTo.item(0);
			m.To_Id = Integer.parseInt(getCharacterDataFromElement(line));

			NodeList userIdFrom = element.getElementsByTagName("userIdFrom");
			line = (Element) userIdFrom.item(0);
			m.From_Id = Integer.parseInt(getCharacterDataFromElement(line));

			NodeList date = element.getElementsByTagName("date");
			line = (Element) date.item(0);
			m.date = getCharacterDataFromElement(line);

			arrMess.add(m);

		}

		SQLiteDatabase sqldb = this.getWritableDatabase();

		for (int i = 0; i < arrMess.size(); i++) {

			String sql = "INSERT INTO message (Message_Id,To_Id,From_Id,Body,Date) VALUES("
					+ arrMess.get(i).Message_Id
					+ ", "
					+ +arrMess.get(i).To_Id
					+ ", " + +arrMess.get(i).From_Id + ", \"" +

					arrMess.get(i).body + "\", \"" +

					arrMess.get(i).date + "\")";

			sqldb.execSQL(sql);

		}

		sqldb.close();

	}

	public ArrayList<Message> getAll(int userNum) {
		SQLiteDatabase sqldb = this.getWritableDatabase();

		String sql = "Select * from message where To_Id = " + userNum;

		Cursor cursor = sqldb.rawQuery(sql, null);

		ArrayList<Message> toReturn = new ArrayList<Message>();

		if (cursor.moveToFirst()) {

			do {
				Message m = new Message();
				m.Message_Id = Integer.parseInt(cursor.getString(0));
				m.To_Id = Integer.parseInt(cursor.getString(1));
				m.From_Id = Integer.parseInt(cursor.getString(2));
				m.body = cursor.getString(3);
				m.date = cursor.getString(4);

				toReturn.add(m);

			} while (cursor.moveToNext());

		}
		cursor.close();

		sqldb.close();

		// return contact list
		return toReturn;


	}
	
	public ArrayList<Message> getAllSaved(int userNum)
	{
		SQLiteDatabase sqldb = this.getWritableDatabase();
		
		String sql = "Select * from message_saved where To_Id = "+userNum;
		
		Cursor cursor = sqldb.rawQuery(sql, null);
		

		ArrayList<Message> toReturn = new ArrayList<Message>();


		if (cursor.moveToFirst()) {

				do{
					Message m = new Message();
					m.Message_Id = Integer.parseInt(cursor.getString(0));
					m.To_Id = Integer.parseInt(cursor.getString(1));
					m.From_Id = Integer.parseInt(cursor.getString(2));
					m.body = cursor.getString(3);
					m.date = cursor.getString(4);
					
					toReturn.add(m);
					
					
				}while(cursor.moveToNext());

		}
		cursor.close();

		sqldb.close();

		// return contact list
		return toReturn;
	}

	public static String getCharacterDataFromElement(Element e) {
		Node child = e.getFirstChild();
		if (child instanceof CharacterData) {
			CharacterData cd = (CharacterData) child;
			return cd.getData();
		}
		return "";
	}


	public void insertMessageIntoSavedDatabase(String toStr, String fromStr,
			String bodyStr,int messageId) {
		SQLiteDatabase sqldb = this.getWritableDatabase();

		String sql = "INSERT INTO message_saved (Message_Id,To_Id,From_Id,Body,Date) VALUES("
				+ "NULL, " +
				"\""+toStr+"\", "+
				"\""+fromStr+"\", "+
				"\""+bodyStr+"\", "+
				"datetime())";


		sqldb.execSQL(sql);
		
		sql = "DELETE FROM message where Message_Id = "+ messageId;

		sqldb.execSQL(sql);

		sqldb.close();
		
	}

	public void deleteMessageFromDatabase(int messageId, String toStr,
			String fromStr) {
		
		SQLiteDatabase db = this.getWritableDatabase();

		String sql = "DELETE FROM message WHERE Message_Id =" + messageId;

		db.execSQL(sql);

		db.close();
	}
	
	public void deleteMessageFromSavedDatabase(int messageId, String toStr,
			String fromStr) {
		
		SQLiteDatabase db = this.getWritableDatabase();

		String sql = "DELETE FROM message_saved WHERE Message_Id =" + messageId;

		db.execSQL(sql);

		db.close();
	}

}
